CREATE DATABASE VNEYE
GO
USE VNEYE
GO
CREATE TABLE Services(
 S_ID INT IDENTITY(1,1) PRIMARY KEY,
 S_Name NVARCHAR(50) NOT NULL,
 S_Description NVARCHAR(200) NOT NULL,
 S_Content NTEXT	NOT NULL,
 STATUS BIT DEFAULT(0)
)
GO
CREATE TABLE SlideShows(
 ID INT IDENTITY(1,1) PRIMARY KEY,
 S_ID INT REFERENCES Services(S_ID),
 Title NVARCHAR(20) NOT NULL,
 IMAGE VARCHAR(50) NOT NULL	,
 STATUS BIT DEFAULT(0)
)
GO
CREATE TABLE Customers(
 	C_ID INT IDENTITY(1,1) PRIMARY KEY,
 	C_Username VARCHAR(100) NOT NULL,
 	C_Password VARCHAR(200) NOT NULL,
 	C_Fullname NVARCHAR(200) NOT NULL,
 	C_Gender VARCHAR(10) NOT NULL,
 	C_Address NVARCHAR(500) NOT NULL,
 	C_Phone VARCHAR(200) NOT NULL,
 	C_Email VARCHAR(50) NOT NULL,
 	C_Status BIT DEFAULT(0)
)
GO
CREATE TABLE FeedBacks(
 F_ID INT IDENTITY(1,1) PRIMARY KEY,
 C_ID INT REFERENCES Customers(C_ID),
 F_Title NVARCHAR(300) NOT NULL,
 F_Content NTEXT NOT NULL,
 F_File NVARCHAR(200),
 F_Status BIT DEFAULT(0)	
)

--- Store procedure

-- get all services
CREATE PROC getAllServices
AS
BEGIN
	SELECT * FROM Services s 
END
--get slide show with service avaliable
CREATE PROC getSlideWithServiceAvaliable
AS
BEGIN
	SELECT ss.ID,ss.S_ID,s.S_Name,ss.Title,ss.[IMAGE],ss.[STATUS]
	FROM SlideShows ss INNER JOIN Services s ON s.S_ID = ss.S_ID WHERE s.[STATUS]=1
END
--get slide avaliable
CREATE PROC getSlideAvaliable
AS
BEGIN
	SELECT ss.ID,ss.S_ID,s.S_Name,ss.Title,ss.[IMAGE],ss.[STATUS] 
	FROM SlideShows ss INNER JOIN Services s ON s.S_ID = ss.S_ID 
	WHERE s.[STATUS]=1 AND ss.[STATUS]=1
END
-- add new service
CREATE PROC AddService
@name NVARCHAR(50),
@description NVARCHAR(200),
@content NTEXT
AS
BEGIN
	INSERT INTO Services
	(
		-- S_ID -- this column value is auto-generated
		S_Name,
		S_Description,
		S_Content,
		[STATUS]
	)
	VALUES
	(
		@name,
		@description,
		@content,
		1
		
	)
END
--get Service by id
CREATE PROC getServiceById
@id INT
AS
BEGIN
	SELECT
		s.S_ID,
		s.S_Name,
		s.S_Description,
		s.S_Content,
		s.[STATUS]
	FROM
		Services s
	WHERE s.S_ID=@id	
END

--update service

CREATE PROC updateServiceById
@id INT,
@name NVARCHAR(50),
@description NVARCHAR(200),
@content NTEXT,
@status BIT
AS
BEGIN
	UPDATE Services
	SET
		-- S_ID = ? -- this column value is auto-generated
		S_Name = @name,
		S_Description = @description,
		S_Content = @content,
		[STATUS] = @status
	WHERE S_ID=@id	
END
--Update status service

CREATE PROC updateStatusServiceById
@id INT,
@status BIT
AS
BEGIN
	UPDATE Services
	SET
		[STATUS] = @status
	WHERE S_ID=@id	
END